﻿
/*
 * Update the schema of the table UDS_Flow_Path
 */
 
/* 1. change the column definitions */
ALTER TABLE dbo.UDS_Flow_Path ALTER COLUMN Doc_ID int NOT NULL;
ALTER TABLE dbo.UDS_Flow_Path ALTER COLUMN Order_ID int NOT NULL;
ALTER TABLE dbo.UDS_Flow_Path ALTER COLUMN Flow_ID int NOT NULL;
ALTER TABLE dbo.UDS_Flow_Path ALTER COLUMN Step_ID int NOT NULL;
ALTER TABLE dbo.UDS_Flow_Path ALTER COLUMN Staff_ID int NOT NULL;
GO


/* 2. Add PK */
IF object_id(N'dbo.[PK_UDS_Flow_Path]') IS NOT NULL
    ALTER TABLE [dbo].[UDS_Flow_Path] DROP CONSTRAINT [PK_UDS_Flow_Path];
IF object_id(N'dbo.[FK_UDS_Flow_Path_DocID]') IS NOT NULL
    ALTER TABLE [dbo].[UDS_Flow_Path] DROP CONSTRAINT [FK_UDS_Flow_Path_DocID];
IF object_id(N'dbo.[FK_UDS_Flow_Path_StaffID]') IS NOT NULL
    ALTER TABLE [dbo].[UDS_Flow_Path] DROP CONSTRAINT [FK_UDS_Flow_Path_StaffID];
IF object_id(N'dbo.[FK_UDS_Flow_Path_FlowStep]') IS NOT NULL
    ALTER TABLE [dbo].[UDS_Flow_Path] DROP CONSTRAINT [FK_UDS_Flow_Path_FlowStep];

ALTER TABLE dbo.UDS_Flow_Path ADD
    CONSTRAINT [PK_UDS_Flow_Path] PRIMARY KEY CLUSTERED (Doc_ID, Order_ID),
    CONSTRAINT [FK_UDS_Flow_Path_DocID] FOREIGN KEY (Doc_ID) REFERENCES dbo.UDS_Flow_Style_Data (Doc_ID),
    CONSTRAINT [FK_UDS_Flow_Path_StaffID] FOREIGN KEY (Staff_ID) REFERENCES dbo.UDS_Staff(Staff_ID),
    CONSTRAINT [FK_UDS_Flow_Path_FlowStep] FOREIGN KEY (Flow_ID, Step_ID) REFERENCES dbo.UDS_Flow_Step(Flow_ID, Step_ID);
GO